# 表级锁

# 1. 表锁的基本概念

表级锁每次操作锁住整张表,锁定粒度大,发生锁冲突的概率大,并发度最低,支持Myisam、InnoDB、BDB等存储引擎.

  • 表级锁主要分为以下三类
1) 表锁
2) 元数据锁 (meta data lock,MDL)
3) 意向锁
1
2
3
-- 加锁
lock table $table_name read/write;

-- 释放锁
unlock tables/ 直接断开客户端连接

/*
1. 如果一个客户端 A 对表 t1 加了读锁,则客户端 B 可以读(但是不可以当前读)

2. 当前读操作也被阻塞
select * from $table_name for update;
*/
1
2
3
4
5
6
7
8
9
10
11
12

# 2. 表锁的使用(read/write lock)

session1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

/*read lock*/
-- 加锁
mysql> lock tables course read;
Query OK, 0 rows affected (0.00 sec)

-- 正常读
mysql> select * from course;
+----+--------+
| id | name   |
+----+--------+
|  1 | Java   |
|  2 | PHP    |
|  3 | MySQL  |
|  4 | Hadoop |
+----+--------+
4 rows in set (0.00 sec)

-- 当前回话更新直接报错
mysql> update course set name='Golang' where id = 4;
ERROR 1100 (HY000): Table 'student_course' was not locked with LOCK TABLES

-- 释放锁
mysql> unlock tables;


/*write lock*/
mysql> lock tables course write;

/* 
1. 自己可以读写读写都阻塞
2. 其他会话读写都会阻塞
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

session2

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 正常读
mysql> select * from course;
+----+--------+
| id | name   |
+----+--------+
|  1 | Java   |
|  2 | PHP    |
|  3 | MySQL  |
|  4 | Hadoop |
+----+--------+
4 rows in set (0.00 sec)

-- 写阻塞
mysql> update course set name='Golang' where id = 4;

-- session1 释放锁之后正常更新,阻塞时长: 1 min 44.98 sec
mysql> update course set name='Golang' where id = 4;
Query OK, 1 row affected (1 min 44.98 sec)
Rows matched: 1  Changed: 1  Warnings: 0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 3. 元数据锁

Mysql 自己内部维护的锁(自动加锁解锁),用于保证 DML & DQL 语句和 DDL 语句不会冲突

1) 当执行 DML & DQL 语句时,会自动对当前表加 MDL 读锁
2) 当执行 DDL 语句修改表结构时,会自动对当前表加 MDL 写锁
3) MDL 锁主要作用是维护表元数据的数据一致性,在表上有活动事务的时候,不可以对元数据进行写入操作
4) 当一张增删改查的时候,加MDL读锁(共享锁); 当对表结构进行变更操作时,加MDL写锁(排他锁)
1
2
3
4
对应sql 锁类型 说明
locak tables $table read/write SHARED_READ_ONLY/SHARED_READ_ONLY_WRITE -
select/select ... lock in share mode SHARED_READ 与SHARED_READ/SHARED_WRITE兼容,EXCLUSIVE互斥
insert/update/delete/select ... for update SHARED_READ_write 与SHARED_READ/SHARED_WRITE兼容,EXCLUSIVE互斥
alert table... EXCLUSIVE 与其他MDL都互斥

查询元数据锁

mysql> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
+-------------+--------------------+----------------+------------------+---------------+
| object_type | object_schema      | object_name    | lock_type        | lock_duration |
+-------------+--------------------+----------------+------------------+---------------+
| TABLE       | my_test            | course         | SHARED_READ_ONLY | TRANSACTION   |
| TABLE       | performance_schema | metadata_locks | SHARED_READ      | TRANSACTION   |
+-------------+--------------------+----------------+------------------+---------------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9

# 4. 意向锁

Mysql 自己内部维护的锁,避免行级锁 & 表级锁冲突,当加行级锁时,会自动为该表加意向锁,当需要加表级锁时会检查意向锁,如果没有意向锁,则加锁成功

1) 意向共享锁(IS):由语句select … lock in share mode添加。与表锁共享锁(read)兼容,与表锁排他锁(write)互斥。
2) 意向排他锁(IX):由insert、update、delete、select…for update添加。与表锁共享锁(read)及排他锁(write)都互斥,意向锁之间不会互斥。
意向锁的作用就是当一个事务在需要获取资源锁定的时候,如果遇到自己需要的资源已经被排他锁占用的时候,该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁,那么就在表上面添加一个意向共享锁。而如果自己需要的是某行(或者某些行)上面添加一个排他锁的话,则先在表上面添加一个意向排他锁。

意向共享锁可以同时并存多个,但是意向排他锁同时只能有一个存在。
所以,可以说InnoDB的锁定模式实际上可以分为四种:共享锁(S),排他锁(X),意向共享锁(IS)和意向排他锁(IX)


意向锁更多是一个标志作用,意向锁之间不会排斥就已经说明了这点。
当我们添加的行锁类型为共享锁时,意向锁的类型就为共享锁,当我们添加的行锁类型为排他锁时,意向锁的类型就为排他锁。
需要注意的是,一旦事务提交了,意向共享锁、意向排他锁,都会自动释放。
1
2
3
4
5
6
7
8
9
10
11

意向锁查看使用

  • session1
-- 开启一个事务
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)

-- 加上一个行锁
mysql> select * from course where id = 1 lock in share mode;
+----+------+
| id | name |
+----+------+
|  1 | Java |
+----+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
  • session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 可以常看到course中存在两个意向锁
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| my_test       | course      | NULL       | TABLE     | IS            | NULL      |
| my_test       | course      | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)

-- 分别加读锁
mysql> lock tables course read;
Query OK, 0 rows affected (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

/*
当我们添加表共享读锁时,是能成功添加的,因为此时意向锁的类型是共享锁,但是当我们添加表独占写锁时,就会被阻塞
下边可以看出,一直阻塞到session1中涉及锁的回话已经提交了,独享锁才成功.
*/
-- 写锁阻塞
mysql> lock table course write;
Query OK, 0 rows affected (1 min 20.28 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Last Updated: 5/5/2023, 2:33:10 AM